from odoo import http, _
from odoo.http import request
import io
import xlsxwriter


class MommyDocumentsController(http.Controller):
    @http.route("/mommy_documents/ddt/<int:wizard_id>/xlsx", type="http", auth="user")
    def ddt_xlsx(self, wizard_id, **kwargs):
        wizard = request.env["documents.ddt.wizard"].browse(wizard_id).sudo()
        if not wizard.exists():
            return request.not_found()

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})

        # Common formats
        fmt_title = workbook.add_format({"bold": True, "font_size": 18, "align": "center"})
        fmt_subtitle = workbook.add_format({"bold": True, "font_size": 12, "align": "center"})
        fmt_bold = workbook.add_format({"bold": True})
        fmt_border = workbook.add_format({"border": 1})
        fmt_border_bold = workbook.add_format({"border": 1, "bold": True})
        fmt_small = workbook.add_format({"font_size": 9})
        fmt_center = workbook.add_format({"align": "center"})
        fmt_center_bold = workbook.add_format({"align": "center", "bold": True})
        fmt_border_wrap = workbook.add_format({"border": 1, "text_wrap": True})
        fmt_center_tlr = workbook.add_format({"align": "center", "top": 1, "left": 1, "right": 1})
        fmt_subtitle_tlr = workbook.add_format({"bold": True, "font_size": 18, "align": "center", "top": 1, "left": 1, "right": 1})
        # Custom formats for header area
        fmt_center_lr = workbook.add_format({"align": "center", "left": 1, "right": 1})
        fmt_right_lr = workbook.add_format({"align": "right", "left": 1, "right": 1})
        fmt_lr = workbook.add_format({"left": 1, "right": 1})
        fmt_lr_bold = workbook.add_format({"left": 1, "right": 1, "bold": True})
        fmt_right_lrb_bold = workbook.add_format({"align": "right", "left": 1, "right": 1, "bottom": 1})
        fmt_center_lrb_bold = workbook.add_format({"align": "center", "left": 1, "right": 1, "bottom": 1})
        # Border + centered formats for tables
        fmt_border_center = workbook.add_format({"border": 1, "align": "center"})
        fmt_border_center_bold = workbook.add_format({"border": 1, "align": "center", "bold": True})

        def _tri(text_zh, text_en, text_it):
            if wizard.language_mode == "zh":
                return text_zh
            if wizard.language_mode == "en":
                return text_en
            if wizard.language_mode == "it":
                return text_it
            # tri-lingual stacked
            return f"{text_zh}\n{text_en}\n{text_it}"

        def _sheet_name(text_zh, text_en, text_it):
            # Use the same language logic, but ensure the worksheet name is valid
            name = _tri(text_zh, text_en, text_it).replace("\n", " / ")
            # Excel limits sheet names to 31 characters and forbids certain characters
            for ch in ['\\\n', ":", "\\", "/", "?", "*", "[", "]"]:
                name = name.replace(ch, " ")
            return name[:31] if len(name) > 31 else name

        def _write_header(sheet):
            # Column widths for 7 columns layout
            sheet.set_column(0, 0, 12)  # 序号
            sheet.set_column(1, 1, 25)  # 包裹编号
            sheet.set_column(2, 2, 18)  # 类型
            sheet.set_column(3, 3, 12)  # 高
            sheet.set_column(4, 4, 12)  # 宽
            sheet.set_column(5, 5, 12)  # 长
            sheet.set_column(6, 6, 15)  # 最大重量
            sheet.set_default_row(18)

            row = 0
            # 1: 公司名称
            sheet.merge_range(row, 0, row, 6, (wizard.company_id and (wizard.company_id.display_name or wizard.company_id.name)) or "", fmt_subtitle_tlr)
            row += 1
            # 2: 地址
            sheet.merge_range(row, 0, row, 6, (wizard.company_id.partner_id.street) or "", fmt_center_lr)
            row += 1

            zip_code = wizard.company_id.partner_id.zip or ""
            city_name = wizard.company_id.partner_id.city or ""

            # 使用 f-string 组合，中间加一个空格。如果其中一个为空，结果就是另一个的值。
            address_str = f"{zip_code} {city_name} ITALIA".strip()

            sheet.merge_range(row, 0, row, 6, address_str, fmt_center_lr)
            row += 1
            # 3: 增值税号
            sheet.merge_range(row, 0, row, 6, _tri("增值税号", "VAT", "PARTITA IVA") + f": {wizard.company_id.vat or ''}", fmt_center_lrb_bold)
            row += 1
            # 4: 保持不变的法条行
            sheet.merge_range(row, 0, row, 6, _tri("运输单据", "Transport Document", "DOCUMENTO DI TRASPORTO") + ": (D.D.T.) D.P.R. 472 del 14-08-1996",  fmt_right_lr)
            row += 1
            # 5: 文件编号 与 日期 同行
            number_text = _tri("文件编号", "Number", "Numero") + f": {wizard.auto_number or ''}"
            date_text = _tri("日期", "Date", "Data") + f": {wizard.date or ''}"
            sheet.merge_range(row, 0, row, 6, f"{number_text}    {date_text}", fmt_right_lrb_bold)
            row += 1

            # 6: 客户标签
            sheet.merge_range(row, 0, row, 6, _tri("客户：", "CLIENTE：", "CLIENTE："), fmt_lr_bold)
            row += 1
            # 7: 客户名称
            sheet.merge_range(row, 0, row, 6, (wizard.partner_id and (wizard.partner_id.display_name or wizard.partner_id.name) or ""), fmt_lr)
            row += 1
            # 8: 客户地址
            partner = wizard.partner_id
            street = (partner and (partner.street or "")) or ""
            city = (partner and (partner.city or "")) or ""
            state_name = (partner and partner.state_id and (partner.state_id.name or "")) or ""
            country_name = (partner and partner.country_id and (partner.country_id.name or "")) or ""

            # 与发票导出一致：同一行显示街道、城市、省份、国家，用英文逗号分隔，不显示各自标签
            addr_label = _tri("地址", "Address", "Indirizzo")
            sep = ", "
            value_parts = []
            if street:
                value_parts.append(street)
            if city:
                value_parts.append(city)
            if state_name:
                value_parts.append(state_name)
            if country_name:
                value_parts.append(country_name)
            address_full = f"{sep.join([p for p in value_parts if p])}"

            sheet.merge_range(row, 0, row, 6, address_full, fmt_lr)
            row += 1
            # 9: USCI号和客户信息标签
            sheet.merge_range(row, 0, row, 6, f"{getattr(wizard.partner_id, 'l10n_cn_usci', '') or wizard.partner_id and (wizard.partner_id.vat or '')}", fmt_lr)
            row += 1

            # Transport info block (manual fields)
            transport_text = ""
            if wizard.transport_method == "sea":
                transport_text = _tri("海运", "Sea", "Via mare")
            elif wizard.transport_method == "air":
                transport_text = _tri("空运", "Air", "Via aerea")
            pairs = [
                (_tri("承运商", "Carrier", "Vettore"), (wizard.carrier_id and (wizard.carrier_id.display_name or wizard.carrier_id.name)) or ""),
                (_tri("运输方式", "Transport Method", "Metodo di trasporto"), transport_text),
                (_tri("运单号", "Order", "Ordine"), wizard.order_reference or ""),
                (_tri("车牌号", "License Plate", "Targa"), wizard.license_plate or ""),
                (_tri("集装箱号", "Container", "Container"), wizard.container_no or ""),
                (_tri("密封号", "Seal Number", "Sigillo"), wizard.seal_no or ""),
                (_tri("起运港", "Departure Port", "Porto di partenza"), wizard.departure_port or ""),
                (_tri("毛重(kg)", "Gross weight (kg)", "Peso lordo (kg)"), wizard.gross_weight or ""),
                (_tri("目的港", "Destination Port", "Porto di destinazione"), wizard.destination_port or ""),
                (_tri("净重(kg)", "Net weight (kg)", "Peso netto (kg)"), wizard.net_weight or ""),
                (_tri("取货/开始运输日期", "Pick-up / Beginning of transport", "Data ritiro / Inizio trasporto"), str(wizard.pickup_date or "")),
            ]
            # Track the cell where the Gross weight value is written so we can update it later
            gross_label = _tri("毛重(kg)", "Gross weight (kg)", "Peso lordo (kg)")
            gross_weight_pos = None  # (row_index, col_index)

            # Process pairs two at a time for two-column layout
            for i in range(0, len(pairs), 2):
                left_pair = pairs[i]
                right_pair = pairs[i + 1] if i + 1 < len(pairs) else ("", "")
                
                # Left side: A-C columns
                sheet.write(row, 0, left_pair[0], fmt_border)  # Label in A
                sheet.merge_range(row, 1, row, 2, left_pair[1], fmt_border)  # Value in B-C
                if left_pair[0] == gross_label:
                    gross_weight_pos = (row, 1)
                
                # Right side: D-G columns  
                sheet.write(row, 3, right_pair[0], fmt_border)  # Label in D
                sheet.merge_range(row, 4, row, 6, right_pair[1], fmt_border)  # Value in E-G
                if right_pair[0] == gross_label:
                    gross_weight_pos = (row, 4)
                
                row += 1

            # Note block
            sheet.merge_range(row, 0, row, 6, _tri("注：", "Note:", "Note:"), fmt_border_bold)
            row += 1
            sheet.merge_range(row, 0, row+1, 6, wizard.note or "", fmt_border)
            row += 2

            return row, {"gross_weight_pos": gross_weight_pos}

        def _collect_package_lines():
            StockMoveLine = request.env["stock.move.line"].sudo()
            lines = StockMoveLine.search([
                "|",
                ("result_package_id", "in", wizard.package_ids.ids),
                ("package_id", "in", wizard.package_ids.ids),
            ])
            # Group by package then product
            pack_to_products = {}
            for ml in lines:
                pkg = ml.result_package_id or ml.package_id
                if not pkg:
                    continue
                product = ml.product_id
                qty = ml.qty_done or ml.quantity or 0.0
                pack_to_products.setdefault(pkg.id, {"package": pkg, "products": {}})
                prod_map = pack_to_products[pkg.id]["products"]
                key = product.id
                prod_map.setdefault(key, {"product": product, "qty": 0.0})
                prod_map[key]["qty"] += qty
            return pack_to_products

        def _sheet_detailed():
            sheet = workbook.add_worksheet(_sheet_name("详细版", "Detailed", "Dettagliato"))
            # Print settings: A4 portrait, fit to width, center horizontally, margins; repeat rows 1-5
            try:
                sheet.set_portrait()
                sheet.set_paper(9)
                sheet.fit_to_pages(1, 0)
                sheet.center_horizontally()
                sheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
            except Exception:
                pass
            try:
                sheet.repeat_rows(0, 4)
            except Exception:
                pass
            row, header_info = _write_header(sheet)

            # Packages table header
            sheet.merge_range(row, 0, row, 6, _tri("包裹与产品明细", "Packages and product lines", "Colli e righe prodotto"), fmt_border_bold)
            row += 1
            header_row = row
            sheet.write_row(row, 0, [
                _tri("序号", "serial number", "numero di serie"),
                _tri("包裹/托盘编号", "Package/Pallet", "Collo/Pallet"),
                _tri("包裹类型", "Type", "Tipo"),
                _tri("高(H)", "H", "H"),
                _tri("宽(W)", "W", "W"),
                _tri("长(L)", "L", "L"),
                _tri("最大重量", "MaxW", "MaxW"),
            ], fmt_border_bold)
            sheet.write(header_row, 0, _tri("序号", "serial number", "numero di serie"), fmt_border_center_bold)
            row += 1

            pack_map = _collect_package_lines()
            pallet_count = 0
            total_items = 0
            total_weight = 0.0
            index = 1
            for pkg_rec in wizard.package_ids:
                data = pack_map.get(pkg_rec.id)
                if not data:
                    continue
                pkg = data["package"]
                # Package type and dimensions in separate columns
                package_type = ""
                height_val = ""
                width_val = ""
                length_val = ""
                max_weight_val = ""

                if pkg.package_type_id:
                    package_type = pkg.package_type_id.display_name or ""
                    # 获取厘米单位的尺寸
                    height_cm = pkg.package_type_id.height or 0.0
                    width_cm = pkg.package_type_id.width or 0.0
                    length_cm = pkg.package_type_id.packaging_length or 0.0

                    if height_cm:
                        height_val = f"{height_cm} cm"
                    if width_cm:
                        width_val = f"{width_cm} cm"
                    if length_cm:
                        length_val = f"{length_cm} cm"
                    # MaxW = 体积(立方米) * 1.65 = (H*W*L/1,000,000) * 165 (kg)
                    if height_cm and width_cm and length_cm:
                        maxw = (height_cm * width_cm * length_cm) / 1000000.0 * 165.0
                        total_weight += maxw
                        max_weight_val = f"{maxw:.2f} kg"

                sheet.write(row, 0, index, fmt_border_center)
                sheet.write(row, 1, pkg.name or "", fmt_border)
                sheet.write(row, 2, package_type, fmt_border)
                sheet.write(row, 3, height_val, fmt_border)
                sheet.write(row, 4, width_val, fmt_border)
                sheet.write(row, 5, length_val, fmt_border)
                sheet.write(row, 6, max_weight_val, fmt_border)
                pallet_count += 1
                index += 1
                row += 1

                # product lines header for this package
                sheet.write(row, 0, "", fmt_border_bold)
                sheet.write(row, 1, _tri("产品编号", "Product", "Prodotto"), fmt_border_bold)
                sheet.merge_range(row, 2, row, 5, _tri("描述", "Description", "Descrizione"), fmt_border_bold)
                sheet.write(row, 6, _tri("数量", "Quantity", "Quantità"), fmt_border_bold)
                row += 1
                package_total = 0
                for prod_data in data["products"].values():
                    product = prod_data["product"]
                    qty = prod_data["qty"]
                    total_items += qty
                    package_total += qty
                    # Product code with barcode below (wrap)
                    product_code_text = product.default_code or ""
                    
                    sheet.write(row, 0, "", fmt_border)
                    sheet.write(row, 1, product_code_text, fmt_border_wrap)
                    sheet.merge_range(row, 2, row, 5, product.name or "", fmt_border)
                    sheet.write(row, 6, qty, fmt_border)
                    row += 1
                # per-package total row
                sheet.write(row, 0, "", fmt_border_bold)
                sheet.write(row, 1, "", fmt_border_bold)
                sheet.merge_range(row, 2, row, 5, _tri("产品合计", "Product total", "Totale prodotti"), fmt_border_bold)
                sheet.write(row, 6, package_total, fmt_border_bold)
                row += 1
                # blank line after each package total
                sheet.write(row, 0, "", fmt_border_bold)
                sheet.write(row, 1, "", fmt_border_bold)
                sheet.merge_range(row, 2, row, 5, "", fmt_border_bold)
                sheet.write(row, 6, "", fmt_border_bold)
                row += 1

            # summary row (layout: A/B unchanged; C label, D value for weight; E-F label; G value for items)
            sheet.write(row, 0, _tri("合计包裹数", "Total packages", "Totale colli"), fmt_border_bold)
            sheet.write(row, 1, pallet_count, fmt_border_bold)
            sheet.write(row, 2, _tri("重量合计(kg)", "Weight total (kg)", "Peso totale (kg)"), fmt_border_bold)
            sheet.write(row, 3, f"{total_weight:.2f}", fmt_border_bold)
            sheet.merge_range(row, 4, row, 5, _tri("产品总数量", "Total item qty", "Quantità totale articoli"), fmt_border_bold)
            sheet.write(row, 6, total_items, fmt_border_bold)
            # write gross weight into header if position known
            gw_pos = header_info.get("gross_weight_pos")
            if gw_pos:
                gw_row, gw_col_start = gw_pos
                try:
                    sheet.merge_range(gw_row, gw_col_start, gw_row, 6 if gw_col_start == 4 else 2, f"{total_weight:.2f}", fmt_border)
                except Exception:
                    sheet.write(gw_row, gw_col_start, f"{total_weight:.2f}", fmt_border)
            row += 1
            # Signature blocks - three equally divided merged areas
            sheet.merge_range(row, 0, row, 1, _tri("寄件人签名", "Sender Signature", "Firma Mittente"), fmt_border_bold)
            sheet.merge_range(row, 2, row, 3, _tri("承运商签名", "Carrier Signature", "Firma Vettore"), fmt_border_bold)
            sheet.merge_range(row, 4, row, 6, _tri("收件人签名", "Recipient Signature", "Firma Destinatario"), fmt_border_bold)
            row += 1
            # Empty signature spaces (3 rows high for signing) - merged per block
            sheet.merge_range(row, 0, row + 2, 1, "", fmt_border)
            sheet.merge_range(row, 2, row + 2, 3, "", fmt_border)
            sheet.merge_range(row, 4, row + 2, 6, "", fmt_border)
            row += 3
            # Define print area from A1 to G{row}
            try:
                sheet.print_area(0, 0, max(0, row), 6)
            except Exception:
                pass

        def _sheet_brief():
            sheet = workbook.add_worksheet(_sheet_name("简要版", "Brief", "Semplificato"))
            # Print settings: A4 portrait, fit to width, center horizontally, margins; repeat rows 1-5
            try:
                sheet.set_portrait()
                sheet.set_paper(9)
                sheet.fit_to_pages(1, 0)
                sheet.center_horizontally()
                sheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
            except Exception:
                pass
            try:
                sheet.repeat_rows(0, 4)
            except Exception:
                pass
            row, header_info = _write_header(sheet)

            sheet.merge_range(row, 0, row, 6, _tri("包裹信息", "Packages", "Colli"), fmt_border_bold)
            row += 1
            header_row = row
            sheet.write_row(row, 0, [
                _tri("序号", "serial number", "numero di serie"),
                _tri("包裹/托盘编号", "Package/Pallet", "Collo/Pallet"),
                _tri("包裹类型", "Type", "Tipo"),
                _tri("高(H)", "H", "H"),
                _tri("宽(W)", "W", "W"),
                _tri("长(L)", "L", "L"),
                _tri("最大重量", "MaxW", "MaxW"),
            ], fmt_border_bold)
            sheet.write(header_row, 0, _tri("序号", "serial number", "numero di serie"), fmt_border_center_bold)
            row += 1

            pallet_count = 0
            total_weight = 0.0
            for index, pkg in enumerate(wizard.package_ids, start=1):
                # Package type and dimensions in separate columns
                package_type = ""
                height_val = ""
                width_val = ""
                length_val = ""
                max_weight_val = ""

                if pkg.package_type_id:
                    package_type = pkg.package_type_id.display_name or ""
                    # 获取厘米单位的尺寸
                    height_cm = pkg.package_type_id.height or 0.0
                    width_cm = pkg.package_type_id.width or 0.0
                    length_cm = pkg.package_type_id.packaging_length or 0.0

                    if height_cm:
                        height_val = f"{height_cm} cm"
                    if width_cm:
                        width_val = f"{width_cm} cm"
                    if length_cm:
                        length_val = f"{length_cm} cm"
                    # MaxW = 体积(立方米) * 1.65 = (H*W*L/1,000,000) * 165 (kg)
                    if height_cm and width_cm and length_cm:
                        maxw = (height_cm * width_cm * length_cm) / 1000000.0 * 165.0
                        total_weight += maxw
                        max_weight_val = f"{maxw:.2f} kg"

                sheet.write(row, 0, index, fmt_border_center)
                sheet.write(row, 1, pkg.name or "", fmt_border)
                sheet.write(row, 2, package_type, fmt_border)
                sheet.write(row, 3, height_val, fmt_border)
                sheet.write(row, 4, width_val, fmt_border)
                sheet.write(row, 5, length_val, fmt_border)
                sheet.write(row, 6, max_weight_val, fmt_border)
                pallet_count += 1
                row += 1

            # summary row (one line)
            pack_map = _collect_package_lines()
            total_items = 0
            for data in pack_map.values():
                for prod_data in data["products"].values():
                    total_items += prod_data["qty"]
            sheet.write(row, 0, _tri("合计包裹数", "Total packages", "Totale colli"), fmt_border_bold)
            sheet.write(row, 1, pallet_count, fmt_border_bold)
            # 与详细版一致：C 列为标签，D 列为数值
            sheet.write(row, 2, _tri("重量合计(kg)", "Weight total (kg)", "Peso totale (kg)"), fmt_border_bold)
            sheet.write(row, 3, f"{total_weight:.2f}", fmt_border_bold)
            sheet.merge_range(row, 4, row, 5, _tri("产品总数量", "Total item qty", "Quantità totale articoli"), fmt_border_bold)
            sheet.write(row, 6, total_items, fmt_border_bold)
            # write gross weight into header if position known
            gw_pos = header_info.get("gross_weight_pos")
            if gw_pos:
                gw_row, gw_col_start = gw_pos
                try:
                    sheet.merge_range(gw_row, gw_col_start, gw_row, 6 if gw_col_start == 4 else 2, f"{total_weight:.2f}", fmt_border)
                except Exception:
                    sheet.write(gw_row, gw_col_start, f"{total_weight:.2f}", fmt_border)
            row += 1
            # Signature blocks - three equally divided merged areas
            sheet.merge_range(row, 0, row, 1, _tri("寄件人签名", "Sender Signature", "Firma Mittente"), fmt_border_bold)
            sheet.merge_range(row, 2, row, 3, _tri("承运商签名", "Carrier Signature", "Firma Vettore"), fmt_border_bold)
            sheet.merge_range(row, 4, row, 6, _tri("收件人签名", "Recipient Signature", "Firma Destinatario"), fmt_border_bold)
            row += 1
            # Empty signature spaces (3 rows high for signing) - merged per block
            sheet.merge_range(row, 0, row + 2, 1, "", fmt_border)
            sheet.merge_range(row, 2, row + 2, 3, "", fmt_border)
            sheet.merge_range(row, 4, row + 2, 6, "", fmt_border)
            row += 3
            # Define print area from A1 to G{row}
            try:
                sheet.print_area(0, 0, max(0, row), 6)
            except Exception:
                pass

        # Build sheet according to detail level
        if wizard.detail_level == "detailed":
            _sheet_detailed()
        else:
            _sheet_brief()

        workbook.close()
        output.seek(0)
        filename = f"{wizard.auto_number or 'DDT'}.xlsx"
        headers = [
            ("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
            ("Content-Disposition", f"attachment; filename=\"{filename}\""),
        ]
        return request.make_response(output.read(), headers=headers) 